package sqlServer;

import com.microsoft.sqlserver.jdbc.SQLServerResultSet;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;

import java.io.File;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.sql.*;

import java.util.ArrayList;
import java.util.LinkedHashSet;
import java.util.List;
import java.util.Set;

public class Main {
    private static final String DRIVER = "com.microsoft.sqlserver.jdbc.SQLServerDriver";
    private static final String URL = "jdbc:sqlserver://139.196.212.145:1433;databasename=";
    private static final String DATABASE_NAME = "master";
    private static final String USER_NAME = "sa";
    private static final String PASSWORD = "jinghe_0713";
    private static final String dbName = "CCCD_HighTrail"; //库名
    static String FilePath = "D:\\测试临时文件\\数据库列表\\中国研究数据服务平台\\公司特色库\\社会经济组织研究\\中国商会研究数据库-CCCD\\";
    static Connection conn;
    static List<String> errorList = new ArrayList<>();

    static {
        try {
            conn = DriverManager.getConnection(URL+DATABASE_NAME,USER_NAME,PASSWORD);
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
    }


    public static void main(String[] args) throws SQLException, IOException {
        List<String> collectionList = GetTableList(dbName);
        for (String collectionName : collectionList){
            System.out.println("collectionName = " + collectionName);
            int collectionSize = GetCollectionSize(collectionName);
            if (collectionSize > 300000){
                errorList.add(collectionName);
                continue;
            }
            /**
             * 获取数据
             */
            ResultSet resultSet = ExecuteQuery("USE " + dbName + " select * from " + "[" + collectionName + "]");
            List<String> firstLine = GetFirstLine(resultSet);
            List<List<String>> dataLine = GetDataLine(resultSet, firstLine);

            WriteToExcel(firstLine, dataLine, collectionName);
        }


        if (errorList.size() == 0)
            return;
        System.out.println("-----------------errorList-------------");
        for (String collectionName: errorList)
            System.out.println(collectionName);
    }

    public static int GetCollectionSize(String collectionName) throws SQLException {
        /**
         * 获取collection 总行数
         */
        ResultSet resultSet = ExecuteQuery("USE " + dbName + " select count(*) from " + "[" + collectionName + "]");
        resultSet.next();
        return Integer.valueOf(resultSet.getString(""));
    }

    public static List<String> GetFirstLine(ResultSet resultSet) throws SQLException {
        /**
         * 取collection的列名 作为firstLine
         */
        List<String> firstLine = new ArrayList<>();
        ResultSetMetaData resultSetMetaData = resultSet.getMetaData();
        final int CollSize = resultSetMetaData.getColumnCount();
        for (int i = 1; i <= CollSize; i++) {
            firstLine.add(resultSetMetaData.getColumnName(i) + "");
        }

        return firstLine;
    }

    public static List<List<String>> GetDataLine(ResultSet resultSet, List<String> KeySet) throws SQLException {
        /**
         * 解析数据
         */
        System.out.println("开始解析数据");
        List<List<String>> dataLine = new ArrayList<>();

        while (resultSet.next()){
            List<String> line = new ArrayList<>();

            for (String key : KeySet){
                line.add(resultSet.getString(key) + "");
            }

            dataLine.add(line);
        }

        System.out.println("解析数据完成");
        return dataLine;
    }

    public static void WriteToExcel(List<String> firstLine, List<List<String>> dataLine, String excelName) throws IOException {
        /**
         * 写入excel
         */
        System.out.println("开始写入");
        Workbook workbook = new SXSSFWorkbook();
        Sheet sheet = workbook.createSheet("Sheet_0");

        int index = 0;
        /**
         * 第一行数据
         */
        Row firstRow = sheet.createRow(0);
        for (String key : firstLine)
            firstRow.createCell(index++).setCellValue(key);

        /**
         * 数据正文
         */
        int RowIndex = 1;
        for (List<String> line : dataLine){
            System.out.println("正在写入----");
            Row row = sheet.createRow(RowIndex++);
            int CellIndex = 0;
            for (String data : line)
                row.createCell(CellIndex++).setCellValue(data);
        }

        workbook.write(new FileOutputStream(FilePath + excelName + ".xlsx"));
        System.out.println("写入完成， 共写入" + RowIndex + "条数据");
    }

    public static List<String> GetTableList(String db_name) throws SQLException {
        /**
         * 获取一个库下的 所有表名
         */

        //use BOPD SELECT * from information_schema.columns
        ResultSet resultSet = ExecuteQuery("USE " + db_name + " select * from information_schema.columns");

        Set<String> collectionSet = new LinkedHashSet<>();
        while (resultSet.next()) {
            String table_name = resultSet.getString("Table_Name");
            collectionSet.add(table_name);
        }

        List<String> collectionList = new ArrayList<>();
        collectionList.addAll(collectionSet);

        System.out.println("成功获取所有表名");
        return collectionList;
    }


    static ResultSet ExecuteQuery(String query){
        /**
         * 对conn.createStatement().executeQuery(query); 进行封装
         */
        try {
            return conn.createStatement().executeQuery(query);
        }catch (Exception e){
            if (!e.getMessage().contains("该语句没有返回结果集"))
                e.printStackTrace();
        }
        return null;
    }
}
